'''
insert into SQ_FISHER (ID, CREATOR, CREDATE, MODIFIER, MODIFYDATE, STATUS, FISHERNAME, OWNERNAME, HAVENID,
CARDNO, PHONE, OWNERADDRESS, OWNERCREDNO, STATECREDNO, FISHERNO, FISHERTYPE, WORKTYPE, FISHERTEXTURE, COMPLETEDATE,
SHIPYARDNAME, SHIPYARDADDRESS, MAINFRAMEPOWER, MAINFRAMETYPE, LENGTH, WIDTH, DEPTH, TONNAGE, NETTONNAGE)
values ('ec57439e786a4b1bb23c8e10d2cf1501',
'超级管理员', to_date('03-07-2017 10:20:25', 'dd-mm-yyyy hh24:mi:ss'),
'超级管理员', to_date('03-07-2017 14:51:52', 'dd-mm-yyyy hh24:mi:ss'),
0, '船名4', '船舶所有人4', '6efaa1e18d704311b318eec25a9a3085', '4', '3', '3', '3', '3', '3', 2, 1, 2,
to_date('23-06-2017', 'dd-mm-yyyy'), '3', '3', 3, '3', 3, 3, 3, 3, 3);
'''
import openpyxl
import re


def zh(value):
    if value == '':
        return "\'\'"
    return value

# fname = r'C:\Users\ZFH\Desktop\渔业船船舶统计表（葵涌街道）.xlsx'
fname = r'C:\Users\ZFH\Desktop\1.xlsx'
wb = openpyxl.load_workbook(fname)
t = wb['Sheet1']

for i in range(1,t.max_row):
    res = t.cell(row=i,column=1).value
    res = re.sub('国内捕捞船','1',res)
    res = re.sub('养殖船','3',res)
    res = re.sub('捕捞辅助船','2',res)
    res = re.sub('其它','2',res)
    res = re.sub('刺网','1',res)
    res = re.sub('木质','2',res)
    res = re.sub('玻璃钢','1',res)
    res = re.sub('围网',r"''",res)
    res = re.sub('拖网',r"''",res)
    res = re.sub('钢质',r"''",res)
    print(res)
#
# t = wb['葵涌街道']
#
# row = t.max_row
# column = 28
#
# lb = []
# l14 = set()
# l15 = set()
# l16 = set()
# for i in range(3, row+1):
#     L = []
#     for j in range(1, column):
#         res = t.cell(row=i, column=j)
#         L.append(res.value)
#     bl = "insert into SQ_FISHER (ID, CREATOR, CREDATE, MODIFIER, MODIFYDATE, STATUS, FISHERNAME, OWNERNAME, HAVENID,\
#     CARDNO, PHONE, OWNERADDRESS, OWNERCREDNO, STATECREDNO, FISHERNO, FISHERTYPE, WORKTYPE, FISHERTEXTURE, COMPLETEDATE,\
#     SHIPYARDNAME, SHIPYARDADDRESS, MAINFRAMEPOWER, MAINFRAMETYPE, LENGTH, WIDTH, DEPTH, TONNAGE, NETTONNAGE)\
#     values (sys_guid()"+",'超级管理员',sysdate,'超级管理员',sysdate, 0,%s'" \
#     "" % L[1] +", '%s'," % L[3] +" '%s'," % L[2] +" '%s'," % L[4] +" '%s'," % L[5] +" '%s', " % L[6] +"" \
#     "'%s'," % L[7] +" '%s'," % L[8] +" '%s'," % L[13] +" %s," % L[14] +" %s," % zh(L[15]) +" %s," % L[16] +"\
#     to_date('%s', 'dd-mm-yyyy')," % L[17] +" '%s', " % L[18] +"'%s'," % L[19] +" %s," % L[20] +" " \
#     "'%s', " % L[21] +"%s, " % L[22] +"%s," % L[23] +" %s," % L[24] +" %s, " % L[25] +"%s);" % L[26]
#     lb.append(bl)
#     l14.add(L[14])
#     l15.add(L[15])
#     l16.add(L[16])
#
# for i in lb:
#     print(i)














